package persistence.Impl;

import persistence.Impl.DBUtil;
import domain.Account;
import persistence.AccountDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDAOImple implements AccountDAO {
    private static final String getAccountByUsernameString = "SELECT\n" +
            "          SIGNON.USERNAME,\n" +
            "          ACCOUNT.EMAIL,\n" +
            "          ACCOUNT.FIRSTNAME,\n" +
            "          ACCOUNT.LASTNAME,\n" +
            "          ACCOUNT.STATUS,\n" +
            "          ACCOUNT.ADDR1 AS address1,\n" +
            "          ACCOUNT.ADDR2 AS address2,\n" +
            "          ACCOUNT.CITY,\n" +
            "          ACCOUNT.STATE,\n" +
            "          ACCOUNT.ZIP,\n" +
            "          ACCOUNT.COUNTRY,\n" +
            "          ACCOUNT.PHONE,\n" +
            "          PROFILE.LANGPREF AS languagePreference,\n" +
            "          PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "          PROFILE.MYLISTOPT AS listOption,\n" +
            "          PROFILE.BANNEROPT AS bannerOption,\n" +
            "          BANNERDATA.BANNERNAME\n" +
            "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "    WHERE ACCOUNT.USERID = ?\n" +
            "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
            "      AND PROFILE.USERID = ACCOUNT.USERID\n" ;
           // +"      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String getAccountByUsernameAndPasswordString = "SELECT \n" +
            "SIGNON.USERNAME, ACCOUNT.EMAIL, ACCOUNT.FIRSTNAME, ACCOUNT.LASTNAME, \n" +
            "ACCOUNT.STATUS, ACCOUNT.ADDR1 AS address1, ACCOUNT.ADDR2 AS address2, ACCOUNT.CITY,  ACCOUNT.STATE, ACCOUNT.ZIP, ACCOUNT.COUNTRY, ACCOUNT.PHONE, \n" +
            "PROFILE.LANGPREF AS languagePreference, PROFILE.FAVCATEGORY AS favouriteCategoryId, PROFILE.MYLISTOPT AS listOption, PROFILE.BANNEROPT AS bannerOption, \n" +
            "BANNERDATA.BANNERNAME \n" +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA \n" +
            "WHERE ACCOUNT.USERID = ?\n" +
            "AND SIGNON.PASSWORD = ?\n" +
            "AND SIGNON.USERNAME = ACCOUNT.USERID \n" +
            "AND PROFILE.USERID = ACCOUNT.USERID \n";
   // +"AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String GETACCOUNT_BY_USERNAME_AND_PASSWORD="SELECT * FROM signon WHERE username=? AND password=?";
    private static final String INSERT_ACCOUNT="INSERT  INTO account  VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String INSERT_SIGNON="INSERT  INTO  signon  VALUES(?,?)";
    private static final String INSERT_PROFILE="INSERT  INTO profile  VALUES(?,?,?,?,?)";
    private static final String UPDATE_ACCOUNT = "UPDATE account SET userid=? ,email = ? , firstname = ? , lastname = ?,status=?,address1=?,address2=?,city=?,state=?,zip=?,country=?,phone=? WHERE userid = ?";
    private static final String UPDATE_PROFILE = "UPDATE profile SET userid=?, langpref = ? , favcategory = ? , mylistopt = ?,banneropt=? WHERE userid = ?";
    private static final String UPDATE_SIGNON = "UPDATE signon SET username=?, password=? WHERE username=?";
    @Override
    public Account getAccountByUsername(String username) {
        Account account = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getAccountByUsernameString);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                account = new Account();
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setState(resultSet.getString(9));
                account.setZip(resultSet.getString(10));
                account.setCountry(resultSet.getString(11));
                account.setPhone(resultSet.getString(12));
                account.setLanguagePreference(resultSet.getString(13));
                account.setFavouriteCategoryId(resultSet.getString(14));
                account.setListOption(resultSet.getBoolean(15));
                account.setBannerOption(resultSet.getBoolean(16));
                account.setBannerName(resultSet.getString(17));

            }
            DBUtil.close(connection,preparedStatement,resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }
        return account;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account account1 = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getAccountByUsernameAndPasswordString);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                //System.out.println("has user");
                account1 = new Account();
                account1.setUsername(resultSet.getString(1));
                account1.setEmail(resultSet.getString(2));
                account1.setFirstName(resultSet.getString(3));
                account1.setLastName(resultSet.getString(4));
                account1.setStatus(resultSet.getString(5));
                account1.setAddress1(resultSet.getString(6));
                account1.setAddress2(resultSet.getString(7));
                account1.setCity(resultSet.getString(8));
                account1.setState(resultSet.getString(9));
                account1.setZip(resultSet.getString(10));
                account1.setCountry(resultSet.getString(11));
                account1.setPhone(resultSet.getString(12));
                account1.setLanguagePreference(resultSet.getString(13));
                account1.setFavouriteCategoryId(resultSet.getString(14));
                account1.setListOption(resultSet.getBoolean(15));
                account1.setBannerOption(resultSet.getBoolean(16));
                account1.setBannerName(resultSet.getString(17));
            }
            DBUtil.close(connection,preparedStatement,resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }
        return account1;

    }

    @Override
    public void insertAccount(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getEmail());
            preparedStatement.setString(3, account.getFirstName());
            preparedStatement.setString(4, account.getLastName());
            preparedStatement.setString(5, account.getStatus());
            preparedStatement.setString(6, account.getAddress1());
            preparedStatement.setString(7, account.getAddress2());
            preparedStatement.setString(8, account.getCity());
            preparedStatement.setString(9, account.getState());
            preparedStatement.setString(10, account.getZip());
            preparedStatement.setString(11, account.getCountry());
            preparedStatement.setString(12, account.getPhone());

            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Override
    public void insertProfile(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2,account.getLanguagePreference());
            preparedStatement.setString(3,account.getFavouriteCategoryId());
            preparedStatement.setBoolean(4,account.isListOption());
            preparedStatement.setBoolean(5,account.isBannerOption());
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2,account.getPassword());
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ACCOUNT);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getEmail());
            preparedStatement.setString(3, account.getFirstName());
            preparedStatement.setString(4, account.getLastName());
            preparedStatement.setString(5, account.getStatus());
            preparedStatement.setString(6, account.getAddress1());
            preparedStatement.setString(7, account.getAddress2());
            preparedStatement.setString(8, account.getCity());
            preparedStatement.setString(9, account.getState());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getZip());
            preparedStatement.setString(12, account.getPhone());
            preparedStatement.setString(13, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PROFILE);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getLanguagePreference());
            preparedStatement.setString(3, account.getFavouriteCategoryId());
            preparedStatement.setBoolean(4, account.isListOption());
            preparedStatement.setBoolean(5, account.isBannerOption());
            preparedStatement.setString(6, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SIGNON);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            preparedStatement.setString(3,account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
//    public static void main(String[] args) {
//        Account account = new Account();
//        account.setUsername("zhou");
//        AccountDAO accountDAO = new AccountDAOImple();
//        accountDAO. insertAccount(account);
//    }
}


